{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 特征工程\n",
    "### 使用找空缺星期天以及空缺节假日的方法将真实日期还原出来。\n",
    "### 节假日之前的around_holiday呈线性递增趋势，节假日之后的around_holiday呈线性递减趋势。\n",
    "### 使用真实日期在数据中提取一些特征，例如该天是否放假，是否是星期天等等。\n",
    "### 最终生成每个brand的dataframe存为csv。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "\n",
    "# 上班的星期天\n",
    "def spring_sunday(ds):\n",
    "    date = pd.to_datetime(ds)\n",
    "    if date.weekday() == 6 and all_data_fill[pd.to_datetime(all_data_fill['date']) == date]['is_work'].values[0] == 1:\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "\n",
    "\n",
    "# 正常的Sunday\n",
    "def is_norm_sunday(ds):\n",
    "    date = pd.to_datetime(ds)\n",
    "    if date.weekday() == 6 and all_data_fill[pd.to_datetime(all_data_fill['date']) == date]['is_work'].values[0] == 0:\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "\n",
    "\n",
    "def is_redundent_sunday(ds):\n",
    "    date = pd.to_datetime(ds)\n",
    "    if date.weekday() == 6 and all_data_fill[pd.to_datetime(all_data_fill['date']) == date]['date_old'].values[0] == 0:\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "\n",
    "\n",
    "# 上班的saturday\n",
    "def is_saturday(ds):\n",
    "    date = pd.to_datetime(ds)\n",
    "    if date.weekday() == 5 and all_data_fill[pd.to_datetime(all_data_fill['date']) == date]['is_work'].values[0] == 1:\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "\n",
    "\n",
    "def is_in_holiday(ds):\n",
    "    date = pd.to_datetime(ds)\n",
    "    if all_data_fill[pd.to_datetime(all_data_fill['date']) == date]['is_holiday'].values[0] == 1:\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "\n",
    "\n",
    "def is_after_new_year(ds):\n",
    "    date = pd.to_datetime(ds)\n",
    "    if date.month == 1 and date.day <= 8:\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "\n",
    "\n",
    "def get_month(ds):\n",
    "    date = pd.to_datetime(ds)\n",
    "    return date.month\n",
    "\n",
    "def get_year(ds):\n",
    "    date = pd.to_datetime(ds)\n",
    "    return date.year\n",
    "\n",
    "def get_day(ds):\n",
    "    date = pd.to_datetime(da)\n",
    "    return date.day\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 生成趋势\n",
    "1. 通过之前A榜的train_data对2016年4月7号之后（没有使用答案）的总上牌量做预测，可以大概得出之后的发展趋势。\n",
    "2. 试过挑选B榜中的几个牌子的总和来做，但效果没使用A榜的预测好。\n",
    "3. 对所以上牌量数据进行log平滑处理。\n",
    "4. 在training set中进行validation，选取lightgbm的参数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:47: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:48: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:49: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:50: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/anaconda3/lib/python3.6/site-packages/pandas/core/indexing.py:537: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  self.obj[item] = s\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'\\n0.13 6 0.8\\n'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import lightgbm\n",
    "from sklearn.metrics import mean_squared_error\n",
    "\n",
    "# train = pd.read_table(\"../data/fusai_train_20180227.txt\", engine='python')\n",
    "# train = train[[\"date\", \"brand\", \"cnt\"]]\n",
    "\n",
    "\n",
    "# train = train[train['brand'].isin([2, 6, 4, 10])]\n",
    "\n",
    "# group_day_t = train.groupby(['date'], as_index=False)['cnt'].agg({'count1': np.sum})\n",
    "d_data = pd.read_csv('../data/train_brand_1/train_brand_1.csv')\n",
    "\n",
    "all_length = 1800\n",
    "split_point = 1191\n",
    "cv_split = 900\n",
    "# all_data = pd.merge(d_data, group_day_t, on='date', how='left')\n",
    "all_data = d_data\n",
    "all_data = all_data.fillna(0)[:all_length]\n",
    "\n",
    "feature_data = all_data[['week_no', 'day_of_week', 'date', 'lunar_day', 'is_holiday', 'after_holiday_type',\n",
    "                         'after_holiday', 'before_holiday', 'before_holiday_type', 'before_holiday',\n",
    "                         'last_workday_before_holiday', 'is_week_end', 'is_work', 'spring_sunday', 'is_norm_sunday',\n",
    "                         'is_saturday', 'is_after_new_year', 'month', 'day', 'year']]\n",
    "feature_data.index = range(len(feature_data))\n",
    "\n",
    "value = list(all_data['cnt_y'].values)\n",
    "ds = list(all_data['ds'].values)\n",
    "\n",
    "train_data = feature_data[:split_point]\n",
    "test_data = feature_data[split_point:all_length]\n",
    "\n",
    "train_value = value[:split_point]\n",
    "train_log = np.log(list(map(lambda x: x + 1, train_value)))\n",
    "\n",
    "test_value = value[cv_split:split_point]\n",
    "\n",
    "\n",
    "lgbm = lightgbm.LGBMRegressor(objective='regression', learning_rate=0.13, max_depth=6,\n",
    "                              n_estimators=200, n_jobs=8, subsample=0.8)\n",
    "lgbm.fit(train_data, train_log, verbose=False)\n",
    "predictions = lgbm.predict(test_data)\n",
    "predictions = list(map(lambda x: x - 1, np.exp(predictions)))\n",
    "\n",
    "test_data.index = range(len(test_data))\n",
    "test_data['test'] = predictions\n",
    "test_data['ds'] = ds[split_point: all_length]\n",
    "train_data['test'] = train_value\n",
    "train_data['ds'] = ds[:split_point]\n",
    "\n",
    "td = pd.concat([train_data, test_data], axis=0)\n",
    "tdd = td[['ds', 'test']]\n",
    "tdd.index = range(len(tdd))\n",
    "tdd.loc[tdd['test'] < 0, 'test'] = 0\n",
    "tdd.to_csv(\"../data/td.csv\")\n",
    "\n",
    "\"\"\"\n",
    "0.13 6 0.8\n",
    "\"\"\"\n",
    "\n",
    "# lr = np.arange(0.01, 0.21, 0.01)\n",
    "# md = np.arange(1, 11, 1)\n",
    "# ss = np.arange(0.1, 1.01, 0.1)\n",
    "# mse_min = 10000000\n",
    "# tp = ()\n",
    "# for l in lr:\n",
    "#     for m in md:\n",
    "#         for s in ss:\n",
    "#             lgbm = lightgbm.LGBMRegressor(objective='regression', learning_rate=l, max_depth=m,\n",
    "#                                           n_estimators=200, n_jobs=8, subsample=s)\n",
    "#             lgbm.fit(train_data, train_log, verbose=False)\n",
    "#             predictions = lgbm.predict(test_data)\n",
    "#             mse = mean_squared_error(test_value, list(map(lambda x: x - 1, np.exp(predictions))))\n",
    "#\n",
    "#             if mse_min > mse:\n",
    "#                 mse_min = mse\n",
    "#                 tp = (l, m, s)\n",
    "# print(mse_min)\n",
    "# print(tp)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 分别对每个brand进行建模\n",
    "1. 分别读每个brand的数据，与之前预测出来的未来趋势进行join，使趋势称为其中一个feature。\n",
    "2. 使用ligtgbm进行回归建模，试过xgboost，效果不如lightgbm。\n",
    "3. 最终的结果使用json格式数据存储"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\n    1: 0.03, 2, 0.8 13431.09 0.15 1, 0.8 13594\\n    2: 0.05, 2, 0.9 10070.78 9493 0.1, 9, 0.5  (0.18 3 0.5 9492 log)\\n    3: 0.02, 7, 0.6 10255.07 10251 0.17, 8, 0.3 (0.17 8 0.3 12031 log)\\n    4: 0.05, 5, 1 11262.57 10018 0.16 3 0.7 (0.16 3 0.7 10022)\\n    5: 0.03, 3, 0.9 32225.13 30818 0.18 8 0.7 (0.18 8 0.7 30815)\\n    6: 0.06, 3, 1 4145.77 3357 0.2 4 0.6  (0.2 4 0.6 3366)\\n    7: 0.08, 2, 0.9 12853.56 12103 0.2 1 0.7 (0.2 1 0.7 12103)\\n    8: 0.08, 5, 0.6 76385.08 56536 0.17 7 0.4 (0.17 7 0.4 56528)\\n    9: 0.08, 10, 1 193139 169557 0.19 5 0.2 (0.19 5 0.2 169551)\\n    10: 0.08, 10, 1 24486 11048 0.01 7 0.6 (0.01 7 0.6 11046)\\n'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import lightgbm\n",
    "from sklearn.metrics import mean_squared_error\n",
    "import xgboost\n",
    "import numpy as np\n",
    "import json\n",
    "\n",
    "\"\"\"\n",
    "    1: 0.03, 2, 0.8 13431.09 0.15 1, 0.8 13594\n",
    "    2: 0.05, 2, 0.9 10070.78 9493 0.1, 9, 0.5  (0.18 3 0.5 9492 log)\n",
    "    3: 0.02, 7, 0.6 10255.07 10251 0.17, 8, 0.3 (0.17 8 0.3 12031 log)\n",
    "    4: 0.05, 5, 1 11262.57 10018 0.16 3 0.7 (0.16 3 0.7 10022)\n",
    "    5: 0.03, 3, 0.9 32225.13 30818 0.18 8 0.7 (0.18 8 0.7 30815)\n",
    "    6: 0.06, 3, 1 4145.77 3357 0.2 4 0.6  (0.2 4 0.6 3366)\n",
    "    7: 0.08, 2, 0.9 12853.56 12103 0.2 1 0.7 (0.2 1 0.7 12103)\n",
    "    8: 0.08, 5, 0.6 76385.08 56536 0.17 7 0.4 (0.17 7 0.4 56528)\n",
    "    9: 0.08, 10, 1 193139 169557 0.19 5 0.2 (0.19 5 0.2 169551)\n",
    "    10: 0.08, 10, 1 24486 11048 0.01 7 0.6 (0.01 7 0.6 11046)\n",
    "\"\"\"\n",
    "# dict1 = {}\n",
    "# for i in range(1, 11):\n",
    "#     # b_data = pd.read_csv('../data/train_brand_1/b.csv')\n",
    "#     # b_data.loc[pd.isna(b_data['date']), 'cnt'] = 0\n",
    "#     td = pd.read_csv('../data/td.csv')\n",
    "#     all_data = pd.read_csv('../data/train_brand_8/train_brand_' + str(i) + '.csv')\n",
    "#     all_data = pd.merge(all_data, td, on='ds', how='left')\n",
    "#     #all_data[np.isnan(all_data['cnt_x'])]['brand_fill'] = 0\n",
    "#     all_data.loc[pd.isna(all_data['cnt_x']), 'brand_fill'] = 0\n",
    "#\n",
    "#     validate_point = 1050\n",
    "#     split_point = 1379\n",
    "#     all_length = 1800\n",
    "#     start = 0\n",
    "#     all_data = all_data[start:]\n",
    "#     all_data = all_data.fillna(0)[:all_length]\n",
    "#     all_data.index = range(len(all_data))\n",
    "#     value = list(all_data['test'].values)\n",
    "#     vt_value = value[validate_point: split_point]\n",
    "#     cnty = list(map(lambda x: x + 1, all_data['test'].values))\n",
    "#     all_data['test'] = np.log(cnty)\n",
    "#\n",
    "#     value = np.log(list(map(lambda x: x + 1, value)))\n",
    "#\n",
    "#     data_feature = all_data[['week_no', 'day_of_week', 'lunar_day', 'is_holiday', 'after_holiday_type',\n",
    "#                              'after_holiday', 'before_holiday_type', 'before_holiday', 'last_workday_before_holiday',\n",
    "#                              'is_week_end', 'is_work', 'spring_sunday', 'test', 'is_norm_sunday', 'is_saturday',\n",
    "#                              'is_after_new_year', 'month', 'day', 'year', 'date', 'brand_fill']]\n",
    "#\n",
    "#     validate_data = data_feature[:validate_point]\n",
    "#     validate_value = value[:validate_point]\n",
    "#\n",
    "#     vt_data = data_feature[validate_point: split_point]\n",
    "#\n",
    "#\n",
    "#\n",
    "#\n",
    "#     lr = np.arange(0.1, 0.31, 0.01)\n",
    "#     md = np.arange(2, 11, 1)\n",
    "#     ss = np.arange(0.1, 1.01, 0.1)\n",
    "#     mse_min = 1000000\n",
    "#     tp = ()\n",
    "#     for l in lr:\n",
    "#         for m in md:\n",
    "#             for s in ss:\n",
    "#                 lgbm = lightgbm.LGBMRegressor(objective='regression', learning_rate=l, max_depth=m,\n",
    "#                                               n_estimators=200, n_jobs=8, subsample=s)\n",
    "#                 lgbm.fit(validate_data, validate_value, verbose=False)\n",
    "#                 predictions = lgbm.predict(vt_data)\n",
    "#                 mse = mean_squared_error(vt_value, list(map(lambda x: x - 1, np.exp(predictions))))\n",
    "#\n",
    "#                 if mse_min > mse:\n",
    "#                     mse_min = mse\n",
    "#                     tp = (l, m, s)\n",
    "#     print(mse_min)\n",
    "#     print(tp)\n",
    "#     dict1[i] = str(tp[0]) + \" \" + str(tp[1]) + \" \" + str(tp[2]) + \" \" + str(mse_min)\n",
    "#\n",
    "# for k, v in dict1.items():\n",
    "#     print(k)\n",
    "#     print(v)\n",
    "#\n",
    "# with open('../data/knn_maps.json', 'w') as f:\n",
    "#     json.dump(dict1, f)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 生成数据\n",
    "生成每个牌子每天的数据，存到data/tb/ 中。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:55: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:55: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:55: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:55: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:55: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:55: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:55: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:55: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:55: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:55: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    }
   ],
   "source": [
    "dict1 = {1: [0.19, 3, 0.8],\n",
    "         2: [0.17, 6, 0.8],\n",
    "         3: [0.19, 9, 0.8],\n",
    "         4: [0.19, 9, 0.8],\n",
    "         5: [0.17, 10, 0.8],\n",
    "         6: [0.15, 10, 0.8],\n",
    "         7: [0.19, 5, 0.8],\n",
    "         8: [0.21, 4, 0.8],\n",
    "         9: [0.16, 9, 0.8],\n",
    "         10: [0.19, 9, 0.8]}\n",
    "\n",
    "\n",
    "split_point = 1191\n",
    "all_length = 1800\n",
    "start = 0\n",
    "\n",
    "for key, val in dict1.items():\n",
    "    td = pd.read_csv('../data/td.csv')\n",
    "    all_data = pd.read_csv('../data/train_brand_1/train_brand_' + str(key) + '.csv')\n",
    "    all_data = pd.merge(all_data, td, on='ds', how='left')\n",
    "    all_data.loc[pd.isna(all_data['cnt_x']), 'brand_fill'] = 0\n",
    "    all_data = all_data[start:]\n",
    "\n",
    "    all_data = all_data.fillna(0)[:all_length]\n",
    "    all_data.index = range(len(all_data))\n",
    "\n",
    "    # cnty = all_data['cnt_y'].values\n",
    "    cnty = list(map(lambda x: x + 1, all_data['test'].values))\n",
    "    all_data['test'] = np.log(cnty)\n",
    "\n",
    "    value = list(all_data['cnt_x'].values)\n",
    "\n",
    "    value = np.log(list(map(lambda x: x + 1, value)))\n",
    "\n",
    "\n",
    "    data_feature = all_data[['week_no', 'day_of_week', 'lunar_day', 'is_holiday', 'after_holiday_type',\n",
    "                             'after_holiday', 'before_holiday_type', 'before_holiday', 'last_workday_before_holiday',\n",
    "                             'is_week_end', 'is_work', 'spring_sunday', 'test', 'is_norm_sunday', 'is_saturday',\n",
    "                             'is_after_new_year', 'month', 'day', 'year', 'date', 'brand_fill']]\n",
    "\n",
    "    train_data = data_feature[:split_point]\n",
    "    train_label = value[:split_point]\n",
    "\n",
    "\n",
    "    test_data = data_feature[split_point:all_length]\n",
    "\n",
    "    lgbm = lightgbm.LGBMRegressor(objective='regression', learning_rate=val[0], max_depth=val[1],\n",
    "                                  n_estimators=200, n_jobs=8, subsample=val[2])\n",
    "    lgbm.fit(train_data, train_label, verbose=False)\n",
    "    predictions = lgbm.predict(test_data)\n",
    "    predictions = list(map(lambda x: x - 1, np.exp(predictions)))\n",
    "\n",
    "    test_data.index = range(len(test_data))\n",
    "    p_value = pd.DataFrame(predictions, columns=['cnt'])\n",
    "    test_data['cnt'] = p_value['cnt']\n",
    "    test_data = test_data[test_data['date'] != 0]\n",
    "\n",
    "    test_data.loc[test_data['cnt'] < 0, 'cnt'] = 20\n",
    "\n",
    "    test_data = test_data[['date', 'cnt', 'brand_fill']]\n",
    "    test_data.to_csv(\"../data/tb2/\" + str(key) + \".csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据整合\n",
    "将各brand的数据整合在一个文件里进行提交。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "\n",
    "\n",
    "length = 600\n",
    "file_dict = {}\n",
    "for f in os.listdir(\"../data/tb2\"):\n",
    "    file_dict[f] = pd.read_csv(\"../data/tb2/\" + f)\n",
    "\n",
    "file_dict = dict(sorted(file_dict.items(), key=lambda x: int(x[0].split(\".\")[0])))\n",
    "# print(file_dict.keys())\n",
    "\n",
    "table = pd.DataFrame()\n",
    "first = True\n",
    "for key, value in file_dict.items():\n",
    "    if first:\n",
    "        table[\"date\"] = file_dict[key][\"date\"]\n",
    "    k = key.split(\".\")[0]\n",
    "    table[k] = file_dict[key][\"cnt\"]\n",
    "    table[k + \"fill\"] = file_dict[key][\"brand_fill\"]\n",
    "table.describe()\n",
    "\n",
    "brand = list(range(1, 11))\n",
    "\n",
    "file_ob = open('../data/sample3.txt', 'w+')\n",
    "for i in range(len(table)):\n",
    "    row = table.iloc[i]\n",
    "    date = row[\"date\"]\n",
    "    for b in brand:\n",
    "        if row[str(b) + \"fill\"] != 0:\n",
    "            string = str(int(date)) + \"\\t\" + str(b) + \"\\t\" + str(int(row[str(b)])) + \"\\n\"\n",
    "            file_ob.write(string)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
